/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import Entity.Hotels;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author MrKoi
 */
public class HotelBean {
    Collection ListHl,Listsearch;
    Connect c=new Connect();
    Connection con;
    CallableStatement cl=null;
    public HotelBean() {
    }
    private void closeCnn(){
        try {
            cl.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(AdminBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public Collection ListHotel()
    {
        con=c.getConnection();
        ListHl=new ArrayList();
        try
        {
            cl=con.prepareCall("{call ListHotel_sp}");
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
                Hotels lc=new Hotels();
                lc.setImage(rs.getString("Image"));
                lc.setHotelID(rs.getInt("HotelID"));
                lc.setNameCity(rs.getString("NameCity"));
                lc.setHotelName(rs.getString("HotelName"));
                lc.setAddress(rs.getString("Address"));
                lc.setStandard(rs.getString("Standard"));
                lc.setPrice(rs.getFloat("Price"));  
                ListHl.add((Object)lc);
            }
           closeCnn();
        }catch (Exception ex){closeCnn();}
            return ListHl;
    }
    //search hotel use search
    public Collection SearchHotel(String namecity,String Pricefrom,String priceto)
    {
        con=c.getConnection();
        Listsearch=new ArrayList();
        try
        {
            int name=Integer.parseInt(namecity);
            float pricef=Float.parseFloat(Pricefrom);
            float pricet=Float.parseFloat(priceto);
            cl=con.prepareCall("{call SearchHotel_sp(?,?,?)}");
            cl.setInt(1,name);
            cl.setFloat(2,pricef);
            cl.setFloat(3,pricet);
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
                Hotels lc=new Hotels();
                lc.setImage(rs.getString("Image"));
                lc.setNameCity(rs.getString("NameCity"));
                lc.setHotelName(rs.getString("HotelName"));
                lc.setAddress(rs.getString("Address"));
                lc.setStandard(rs.getString("Standard"));
                lc.setPrice(rs.getFloat("Price"));  
                Listsearch.add((Object)lc);
            }
            closeCnn();
        }catch (Exception ex){closeCnn();}
            return Listsearch;
    }
  public boolean DeleteHotel(int id)
    {
        con=c.getConnection();
        try
        {
            cl=con.prepareCall("{call DeleteHotel_sp(?)}");
            cl.setInt(1,id);
            int rs=cl.executeUpdate();
            if(rs >0)
            {
                return true;
            }
            closeCnn();
        }catch (Exception ex){return false;}
        return false;
    }
         public boolean InsertHotels(String Image,int locationID,String hotelName,String address, String standard,float price)
        {
            con=c.getConnection();
            try
            {
                cl=con.prepareCall("{call InsertHotels_sp(?,?,?,?,?,?)}");
                cl.setString(1,Image);
                cl.setInt(2,locationID);
                cl.setString(3,hotelName);
                cl.setString(4,address);
                cl.setString(5,standard);
                cl.setFloat(6,price);
                int rs=cl.executeUpdate();
                if(rs >0)
                {
                    return true;
                }
                closeCnn();
            }catch (Exception ex){closeCnn();return false;}
            return false;
        }
         //uodate co image  
       public boolean UpdateYesImageHotel(String image,int locationID,String hotelName,String address, String standard,float price,int HotelID)
         {
         con=c.getConnection();
         try
         {
             cl=con.prepareCall("{call UpdateYesImageHotel_sp(?,?,?,?,?,?,?)}");
             cl.setString("Image",image);
             cl.setInt("LocationID",locationID);
             cl.setString("HotelName",hotelName);
             cl.setString("Address",address);
             cl.setString("Standard",standard);
             cl.setFloat("Price",price);
             cl.setInt("hotelID",HotelID);
             int rs=cl.executeUpdate();
             if(rs >0)
             {
                 return true;
             }
           closeCnn();
         }catch (Exception ex){closeCnn();return false;}
         return false;
     }
         //update ko co image
         public boolean UpdateHotel(int locationID,String hotelName,String address, String standard,float price,int HotelID)
         {
         con=c.getConnection();
         try
         {
             cl=con.prepareCall("{call UpdateHotel_sp(?,?,?,?,?)}");
             cl.setInt("LocationID",locationID);
             cl.setString("HotelName",hotelName);
             cl.setString("Address",address);
             cl.setString("Standard ",standard);
             cl.setFloat("Price",price);
             cl.setInt("hotelID",HotelID);
             int rs=cl.executeUpdate();
             if(rs >0)
             {
                 return true;
             }
           closeCnn();
         }catch (Exception ex){closeCnn();return false;}
         return false;
     }
}
